﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


/// <summary>
/// Summary description for AdminCreateTestDAL
/// </summary>
public class AdminCreateTestDAL
{
	public AdminCreateTestDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    static string connStr = ConfigurationManager.ConnectionStrings["SkillifyConnectionString"].ConnectionString;
    static SqlConnection con = new SqlConnection(connStr);
    //static SqlConnection con = new SqlConnection(@"data source=ANKUR\ANKUR;initial catalog=SkilledRESKAT;integrated security=true");
    static SqlCommand cmd;
    static SqlDataAdapter da;
    static DataTable dt;


    public static DataTable GetSubjects()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


    public static DataTable getAllTopics()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTopicName";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetSubjectsName(int subId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        cmd.Parameters.AddWithValue("@TransType", 0);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetSubjectInfo()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 4);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable DisplayAvailableGroups()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@TransType", 1);
        DataTable DisplayAvailableGroups = new DataTable();
        da.Fill(DisplayAvailableGroups);
        return DisplayAvailableGroups;
    }

    public static DataTable DisplayParticularGroup(int GroupID)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@GroupID", GroupID);
        cmd.Parameters.AddWithValue("@TransType", 4);
        DataTable DisplayParticularGroups = new DataTable();
        da.Fill(DisplayParticularGroups);
        return DisplayParticularGroups;
    }

    public static DataTable DisplayUserRelatedToGroup(int GroupID)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@GroupID", GroupID);
        cmd.Parameters.AddWithValue("@TransType", 5);
        DataTable DisplayParticularGroups = new DataTable();
        da.Fill(DisplayParticularGroups);
        return DisplayParticularGroups;
    }

    public static DataTable displayAllUsers()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@TransType", 2);
        DataTable displayAllUsers = new DataTable();
        da.Fill(displayAllUsers);
        return displayAllUsers;
    }

    public static void  insertGroupInfo(string GroupName,string desc,int active,string userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@GroupName", GroupName);
        cmd.Parameters.AddWithValue("@Desc", desc);
        cmd.Parameters.AddWithValue("@IsActive", active);
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        
    }


    public static void UpdateGroupInfo(int groupId,string GroupName, string desc, int active, string userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@GroupID", groupId);
        cmd.Parameters.AddWithValue("@GroupName", GroupName);
        cmd.Parameters.AddWithValue("@Desc", desc);
        cmd.Parameters.AddWithValue("@IsActive", active);
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@TransType", 6);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

    }


    public static DataTable GetCreaterId(int attemptId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_GetCreaterEmailId";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@AttemptId", attemptId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        DataTable DisplayEmailId = new DataTable();
        da.Fill(DisplayEmailId);
        return DisplayEmailId;
    }

    public static DataTable getTestNames()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestNames";
        cmd.CommandType = CommandType.StoredProcedure;
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable getAssignmentNames()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 13);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataSet GetTestInfo()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataSet GetTestInfoBasedOnSubTopic(int subId,int topicId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetTestInfoBasedOnTopicAndSubject";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subjectid", subId);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        //cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }


    public static DataTable GetTopicNameBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 5);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestRank(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetRank";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TestId", testId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetAssignmentRank(int assId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetRank";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetStudentPerformanceRecord(int groupId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetStudentPerformanceRecord";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@GroupId", groupId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetStudentPerformanceRecordBasedOnUserId(object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetStudentPerformanceRecord";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetChartInformation(object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetStudentPerformanceRecord";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestUserInfoBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 9);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestInfoBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 4);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestIdBasedOnAttemptId(int attemptId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetTestIdBasedOnAttemptId";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AttemptId", attemptId);
        cmd.Parameters.AddWithValue("@TransType",1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetUserIdBasedOnAttemptId(int attemptId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetTestIdBasedOnAttemptId";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AttemptId", attemptId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTopicInfoBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 11);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestStatusInfoBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 8);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


    public static DataTable GetUserName(object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetUser";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetUserEmail(object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetUser";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetUserAssignment(int assId, object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@TransType", 7);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataSet GetTestCompletedListBasedOnStatus(object userId, string status)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestCompletedList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Userid", userId);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataSet GetTestListBasedOnStatus(object userId,string status)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Userid", userId);
        cmd.Parameters.AddWithValue("@Status", status);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataSet GetTestInfoByTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestInfoByTestId";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TestId", testId);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }


    public static DataSet GetAssignInfoByAssignId(int assId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }


    public static DataTable GetQuestionInfo(int subId,int topicId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_ManngeQuestion";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestAttemptInfo(object userId, int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetTestAttempt";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@testId", testId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestAttemptInfoForMail(int attemptId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetTestInfoForMail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AttemptId", attemptId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataSet GetTestCreatedInfoForMail(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetTestInfoForMail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TestId", testId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataTable GetAssignmentAttemptInfoForMail(int assId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetAssignmentInfoForMail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetAssignmentInfoForMail(int assId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetAssignmentInfoForMail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestAttemptAllInfo(object userId, int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetTestAttempt";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@testId", testId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestAttemptAllInfoWithUserInfo(object userId, int testId,string status)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetTestAttempt";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@testId", testId);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@TransType", 6);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


    public static DataSet GetTestAttemptFullInfo(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetTestAttempt";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testId", testId);
        cmd.Parameters.AddWithValue("@TransType", 7);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }



    public static DataSet GetCountOfAssignment(object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@TransType", 11);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataTable GetAssignmentInfo(int subId, int topicId, string status, object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SubjectId", subId);
        cmd.Parameters.AddWithValue("@TopicId", topicId);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@TransType",10 );
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetAssignmentAllInfo(int subId, int topicId,string status)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SubjectId",subId );
        cmd.Parameters.AddWithValue("@TopicId", topicId);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@TransType", 5);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetAssignmentTransAllInformation(int assId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@TransType", 6);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestCompletedAttemptAllInfo(int attId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestCompletedList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AttemptId", attId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTestAttemptQuestionOption(int attId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestCompletedList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AttemptId", attId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetQuestionAllInfoBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 12);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetQuestionInfoBasedOnTestId(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testNo", testId);
        cmd.Parameters.AddWithValue("@TransType", 6);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetSubjectInfoById(int id)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", id);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetQuestionInfoById(int id)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_ManngeQuestion";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@QId", id);
        cmd.Parameters.AddWithValue("@TransType", 4);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetQuestionBySubjectTopic(int subId, string topicId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetQuestionBySubjectTopic";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subjectId", subId);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable getTopicRelatedToSubject(int subId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTopicRelatedToSubject";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetTopicName(int topicId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTopicName";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TopicId", topicId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable setTopic(int subId, string topicName)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_SetTopic";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        cmd.Parameters.AddWithValue("@topicName", topicName);
        //cmd.Parameters.AddWithValue("@TransType", 1);
        //if (con.State == ConnectionState.Open)
        //    con.Close();
        //con.Open();
        //cmd.ExecuteNonQuery();
        //con.Close();

        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable setTest(string testName, int subjectid, string topicId, int isQuestionManual, int noOfQuestion, int noOfHighQuestion, int noOfMediumQuestion, int noOfLowQuestion, int cutOffMarks,
        DateTime startDate, DateTime endDate, int duration, string question, int sendCumulativeReport, int noOfAttempts, int countOfAttempts, string refLibrary, string status, object uid, int notify,object createrId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testName", testName);
        cmd.Parameters.AddWithValue("@subjectid", subjectid);
         cmd.Parameters.AddWithValue("@topicId", topicId);
        cmd.Parameters.AddWithValue("@isQuestionManual", isQuestionManual);
         cmd.Parameters.AddWithValue("@noOfQuestion", noOfQuestion);
        cmd.Parameters.AddWithValue("@noOfHighQuestion", noOfHighQuestion);
         cmd.Parameters.AddWithValue("@noOfMediumQuestion", noOfMediumQuestion);
        cmd.Parameters.AddWithValue("@noOfLowQuestion", noOfLowQuestion);
         cmd.Parameters.AddWithValue("@cutOffMarks", cutOffMarks);
         cmd.Parameters.AddWithValue("@testCreatedDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@startDate", startDate);
         cmd.Parameters.AddWithValue("@endDate", endDate);
         cmd.Parameters.AddWithValue("@duration", duration);
        cmd.Parameters.AddWithValue("@question", question);
         cmd.Parameters.AddWithValue("@sendCumulativeReport", sendCumulativeReport);
        cmd.Parameters.AddWithValue("@noOfAttempts", noOfAttempts);
        cmd.Parameters.AddWithValue("@countOfAttempts", countOfAttempts);
        cmd.Parameters.AddWithValue("@refLibrary", refLibrary);
        cmd.Parameters.AddWithValue("@status", status);
        cmd.Parameters.AddWithValue("@userId", uid);
        cmd.Parameters.AddWithValue("@notify", notify);
        cmd.Parameters.AddWithValue("@CreaterId", createrId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        //if (con.State == ConnectionState.Open)
        //    con.Close();
        //con.Open();
        //cmd.ExecuteNonQuery();
        //con.Close();

        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static void setTestUpdate(string testName, int subjectid, string topicId, int isQuestionManual, int noOfQuestion, int noOfHighQuestion, int noOfMediumQuestion, int noOfLowQuestion, int cutOffMarks,
        DateTime startDate, DateTime endDate, int duration, string question, int sendCumulativeReport, int noOfAttempts, int countOfAttempts, string refLibrary, string status, object uid, int notify, int testid, object createrId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testName", testName);
        cmd.Parameters.AddWithValue("@subjectid", subjectid);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        cmd.Parameters.AddWithValue("@isQuestionManual", isQuestionManual);
        cmd.Parameters.AddWithValue("@noOfQuestion", noOfQuestion);
        cmd.Parameters.AddWithValue("@noOfHighQuestion", noOfHighQuestion);
        cmd.Parameters.AddWithValue("@noOfMediumQuestion", noOfMediumQuestion);
        cmd.Parameters.AddWithValue("@noOfLowQuestion", noOfLowQuestion);
        cmd.Parameters.AddWithValue("@cutOffMarks", cutOffMarks);
        cmd.Parameters.AddWithValue("@testCreatedDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@startDate", startDate);
        cmd.Parameters.AddWithValue("@endDate", endDate);
        cmd.Parameters.AddWithValue("@duration", duration);
        cmd.Parameters.AddWithValue("@question", question);
        cmd.Parameters.AddWithValue("@sendCumulativeReport", sendCumulativeReport);
        cmd.Parameters.AddWithValue("@noOfAttempts", noOfAttempts);
        cmd.Parameters.AddWithValue("@countOfAttempts", countOfAttempts);
        cmd.Parameters.AddWithValue("@refLibrary", refLibrary);
        cmd.Parameters.AddWithValue("@status", status);
        cmd.Parameters.AddWithValue("@userId", uid);
        cmd.Parameters.AddWithValue("@notify", notify);
        cmd.Parameters.AddWithValue("@testNo", testid);
        cmd.Parameters.AddWithValue("@CreaterId", createrId);
        cmd.Parameters.AddWithValue("@TransType", 7);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataTable SetTestAttempt(int testId, string result, int score, int attemptNo, object uid, string qid, string answer, int time)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetTestAttempt";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testId", testId);
        cmd.Parameters.AddWithValue("@Result", result);
        cmd.Parameters.AddWithValue("@Score", score);
        cmd.Parameters.AddWithValue("@AttemptNo", attemptNo);
        cmd.Parameters.AddWithValue("@TimeTaken", time);
        cmd.Parameters.AddWithValue("@UserId", uid);
        cmd.Parameters.AddWithValue("@QuestionId", qid);
        cmd.Parameters.AddWithValue("@Answer", answer);
        cmd.Parameters.AddWithValue("@TransType", 1);
        //if (con.State == ConnectionState.Open)
        //    con.Close();
        //con.Open();
        //cmd.ExecuteNonQuery();
        //con.Close();
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


    public static void updateTestStatus(int testid, object userid,string status)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@status", status);
        cmd.Parameters.AddWithValue("@testNo", testid);
        cmd.Parameters.AddWithValue("@userId", userid);
        cmd.Parameters.AddWithValue("@TransType", 10);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }


    public static void ManngeSubject(string subName, string topicName, int isActive)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subName", subName);
        cmd.Parameters.AddWithValue("@topicName", topicName);
        cmd.Parameters.AddWithValue("@isActive", isActive);
        cmd.Parameters.AddWithValue("@TransType", 3);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataTable GetAssignmentHistory(int assId, object userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@TransType", 9);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static void SetAssignmentStatusStudent(int assId, object userId, string remarks, string filePath)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@Remarks", remarks);
        cmd.Parameters.AddWithValue("@Attachment", filePath);
        cmd.Parameters.AddWithValue("@TransType", 12);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static void SetAssignmentStatus(int assId, object userId, string status,string grade,string remarks)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@Grade", grade);
        cmd.Parameters.AddWithValue("@Remarks", remarks);
        cmd.Parameters.AddWithValue("@TransType",8 );
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataTable SetAssignment(string assignmentName, int subId, int topicId, string Question, string Attachement, DateTime startDt, DateTime endDt, object userId, string status, int notify,int sendReport)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentName",assignmentName);
        cmd.Parameters.AddWithValue("@SubjectId",subId );
        cmd.Parameters.AddWithValue("@TopicId",topicId );
        cmd.Parameters.AddWithValue("@Question",Question );
        cmd.Parameters.AddWithValue("@Attachment",Attachement );
        cmd.Parameters.AddWithValue("@StartDate", startDt);
        cmd.Parameters.AddWithValue("@EndDate",endDt );
        cmd.Parameters.AddWithValue("@Status",status );
        cmd.Parameters.AddWithValue("@Notify",notify );
        cmd.Parameters.AddWithValue("@SendCumulativeReport", sendReport);
        cmd.Parameters.AddWithValue("@UserId",userId );
        cmd.Parameters.AddWithValue("@TransType", 1);
        //if (con.State == ConnectionState.Open)
        //    con.Close();
        //con.Open();
        //cmd.ExecuteNonQuery();
        //con.Close();

        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


    public static void SetAssignmentUpdate(int assId, string assignmentName, int subId, int topicId, string Question, string Attachement, DateTime startDt, DateTime endDt, object userId, string status, int notify,int sendReport)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetAssignment";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AssignmentId", assId);
        cmd.Parameters.AddWithValue("@AssignmentName", assignmentName);
        cmd.Parameters.AddWithValue("@SubjectId", subId);
        cmd.Parameters.AddWithValue("@TopicId", topicId);
        cmd.Parameters.AddWithValue("@Question", Question);
        cmd.Parameters.AddWithValue("@Attachment", Attachement);
        cmd.Parameters.AddWithValue("@StartDate", startDt);
        cmd.Parameters.AddWithValue("@EndDate", endDt);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@Notify", notify);
        cmd.Parameters.AddWithValue("@SendCumulativeReport", sendReport);
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@TransType", 4);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }


    public static void ManngeQuestion(int subId, int topicId, string Question, string option, string answer, string justification, string priority, int multiple,int IsActive)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_ManngeQuestion";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        cmd.Parameters.AddWithValue("@Question", Question);
        cmd.Parameters.AddWithValue("@option", option);
        cmd.Parameters.AddWithValue("@answer", answer);
        cmd.Parameters.AddWithValue("@justification", justification);
        cmd.Parameters.AddWithValue("@priority", priority);
        cmd.Parameters.AddWithValue("@IsMutiple", multiple);
        cmd.Parameters.AddWithValue("@IsActive", IsActive);
        cmd.Parameters.AddWithValue("@TransType", 1);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }


    public static void ManngeQuestionUpdation(int subId, int topicId, string Question, string optionName, string optionId, string answer, string justification, string priority, int multiple, int Qid, int IsActive)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_ManngeQuestion";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        cmd.Parameters.AddWithValue("@Question", Question);
        cmd.Parameters.AddWithValue("@option", optionName);
        cmd.Parameters.AddWithValue("@optionId", optionId);
        cmd.Parameters.AddWithValue("@answer", answer);
        cmd.Parameters.AddWithValue("@justification", justification);
        cmd.Parameters.AddWithValue("@priority", priority);
        cmd.Parameters.AddWithValue("@IsMutiple", multiple);
        cmd.Parameters.AddWithValue("@Qid", Qid);
        cmd.Parameters.AddWithValue("@IsActive", IsActive);
        cmd.Parameters.AddWithValue("@TransType", 3);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static void ManngeSubjectUpdation(int subId, string subName, string topicId, string topicName, int isActive, string allNewTopics)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetSetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", subId);
        cmd.Parameters.AddWithValue("@subName", subName);
        cmd.Parameters.AddWithValue("@topicName", topicName);
        cmd.Parameters.AddWithValue("@topicId", topicId);
        cmd.Parameters.AddWithValue("@isActive", isActive);
        cmd.Parameters.AddWithValue("@newTopicName", allNewTopics);
        cmd.Parameters.AddWithValue("@TransType", 5);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataTable GetMaxAttempt(int testId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetTestAttempt";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testId", testId);
        cmd.Parameters.AddWithValue("@TransType", 4);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
}